package simulaco.dao;

import gminet.infra.dao.DataAccessObject;
import gminet.infra.db.TransactionException;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import simulaco.dto.Concurso;
import simulaco.dto.ConcursoMateria;
import simulaco.dto.ConcursoNivel;
import simulaco.dto.Materia;
import simulaco.dto.Situacao;


public class ConcursoMateriaDAO extends DataAccessObject {

    public static ConcursoMateria findByPK(ConcursoMateria concursoMateria, Connection conn) throws TransactionException {

        return findByPK(concursoMateria.getConcurso().getCodigo(), concursoMateria.getMateria().getCodigo(), conn);
    }

    public static ConcursoMateria findByPK(int concursoId, int materiaId, Connection conn) throws TransactionException {

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("SELECT A.CD_CONCURSO         CD_CONCURSO          ");
        sbSql.append("     , B.CD_INSTITUICAO      CD_INSTITUICAO       ");
        sbSql.append("     , B.CD_ORGAO            CD_ORGAO             ");
        sbSql.append("     , B.TX_INDENTIFICACAO   TX_INDENTIFICACAO    ");
        sbSql.append("     , B.ID_NIVEL            ID_NIVEL             ");
        sbSql.append("     , B.DT_PROVA            DT_PROVA             ");
        sbSql.append("     , B.ID_SITUACAO         ID_SITUACAO_CONCURSO ");
        sbSql.append("     , B.TS_CADASTRO         TS_CADASTRO_CONCURSO ");
        sbSql.append("     , A.CD_MATERIA          CD_MATERIA           ");
        sbSql.append("     , C.TX_NOME             TX_NOME              ");
        sbSql.append("     , C.ID_SITUACAO         ID_SITUACAO_MATERIA  ");
        sbSql.append("     , C.TS_CADASTRO         TS_CADASTRO_MATERIA  ");
        sbSql.append("     , A.ID_SITUACAO         ID_SITUACAO          ");
        sbSql.append("  FROM CONCURSO_MATERIA A ");
        sbSql.append("     , CONCURSO         B ");
        sbSql.append("     , MATERIA          C ");
        sbSql.append(" WHERE A.CD_CONCURSO = B.CD_CONCURSO ");
        sbSql.append("   AND A.CD_MATERIA  = C.CD_MATERIA  ");
        sbSql.append("   AND A.CD_CONCURSO = ? ");
        sbSql.append("   AND A.CD_MATERIA  = ? ");

        ConcursoMateria concursoMateria = null;
        try {
            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            setInt(ps, 1, concursoId);
            setInt(ps, 2, materiaId);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                concursoMateria = fromResultSet(rs);
            }
            close(ps);
        } catch (SQLException sqle) {
            throw new TransactionException("ConcursoMateriaDAO.findByPK (" + sqle.getMessage() + ")");
        }
        return concursoMateria;
    }

    public static List<ConcursoMateria> findByConcurso(int concursoId, Connection conn) throws TransactionException {

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("SELECT A.CD_CONCURSO         CD_CONCURSO          ");
        sbSql.append("     , B.CD_INSTITUICAO      CD_INSTITUICAO       ");
        sbSql.append("     , B.CD_ORGAO            CD_ORGAO             ");
        sbSql.append("     , B.TX_INDENTIFICACAO   TX_INDENTIFICACAO    ");
        sbSql.append("     , B.ID_NIVEL            ID_NIVEL             ");
        sbSql.append("     , B.DT_PROVA            DT_PROVA             ");
        sbSql.append("     , B.ID_SITUACAO         ID_SITUACAO_CONCURSO ");
        sbSql.append("     , B.TS_CADASTRO         TS_CADASTRO_CONCURSO ");
        sbSql.append("     , A.CD_MATERIA          CD_MATERIA           ");
        sbSql.append("     , C.TX_NOME             TX_NOME              ");
        sbSql.append("     , C.ID_SITUACAO         ID_SITUACAO_MATERIA  ");
        sbSql.append("     , C.TS_CADASTRO         TS_CADASTRO_MATERIA  ");
        sbSql.append("     , A.ID_SITUACAO      ID_SITUACAO     ");
        sbSql.append("  FROM CONCURSO_MATERIA A ");
        sbSql.append("     , CONCURSO         B ");
        sbSql.append("     , MATERIA          C ");
        sbSql.append(" WHERE A.CD_CONCURSO = B.CD_CONCURSO ");
        sbSql.append("   AND A.CD_MATERIA  = C.CD_MATERIA  ");
        sbSql.append("   AND A.CD_CONCURSO = ? ");
        List<ConcursoMateria> concursoMateriasList = new ArrayList<ConcursoMateria>(100);
        try {
            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            setInt(ps, 1, concursoId);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                ConcursoMateria concursoMateria = fromResultSet(rs);
                concursoMateriasList.add(concursoMateria);
            }
            close(rs);
        } catch (SQLException sqle) {
            throw new TransactionException("ConcursoMateriaDAO.findByConcurso (" + sqle.getMessage() + ")");
        }
        return concursoMateriasList;
    }

    public static List<ConcursoMateria> findByMateria(int materiaId, Connection conn) throws TransactionException {

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("SELECT A.CD_CONCURSO         CD_CONCURSO          ");
        sbSql.append("     , B.CD_INSTITUICAO      CD_INSTITUICAO       ");
        sbSql.append("     , B.CD_ORGAO            CD_ORGAO             ");
        sbSql.append("     , B.TX_INDENTIFICACAO   TX_INDENTIFICACAO    ");
        sbSql.append("     , B.ID_NIVEL            ID_NIVEL             ");
        sbSql.append("     , B.DT_PROVA            DT_PROVA             ");
        sbSql.append("     , B.ID_SITUACAO         ID_SITUACAO_CONCURSO ");
        sbSql.append("     , B.TS_CADASTRO         TS_CADASTRO_CONCURSO ");
        sbSql.append("     , A.CD_MATERIA          CD_MATERIA           ");
        sbSql.append("     , C.TX_NOME             TX_NOME              ");
        sbSql.append("     , C.ID_SITUACAO         ID_SITUACAO_MATERIA  ");
        sbSql.append("     , C.TS_CADASTRO         TS_CADASTRO_MATERIA  ");
        sbSql.append("     , A.ID_SITUACAO      ID_SITUACAO     ");
        sbSql.append("  FROM CONCURSO_MATERIA A ");
        sbSql.append("     , CONCURSO         B ");
        sbSql.append("     , MATERIA          C ");
        sbSql.append(" WHERE A.CD_CONCURSO = B.CD_CONCURSO ");
        sbSql.append("   AND A.CD_MATERIA  = C.CD_MATERIA  ");
        sbSql.append("   AND A.CD_MATERIA  = ? ");
        List<ConcursoMateria> concursoMateriasList = new ArrayList<ConcursoMateria>(100);
        try {
            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            setInt(ps, 1, materiaId);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                ConcursoMateria concursoMateria = fromResultSet(rs);
                concursoMateriasList.add(concursoMateria);
            }
            close(rs);
        } catch (SQLException sqle) {
            throw new TransactionException("ConcursoMateriaDAO.findByMateria (" + sqle.getMessage() + ")");
        }
        return concursoMateriasList;
    }

    public static void insert(ConcursoMateria concursoMateria, Connection conn) throws TransactionException {

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("INSERT INTO CONCURSO_MATERIA ");
        sbSql.append("     ( CD_CONCURSO   ");
        sbSql.append("     , CD_MATERIA    ");
        sbSql.append("     , ID_SITUACAO   ");
        sbSql.append(") VALUES (?,?,?)     ");
        try {
            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            int pos = 1;
            setInt(ps, pos++, concursoMateria.getConcurso().getCodigo());
            setInt(ps, pos++, concursoMateria.getMateria().getCodigo());
            setInt(ps, pos++, concursoMateria.getSituacao().getCodigo());
            ps.executeUpdate();
            close(ps);
        } catch (SQLException sqle) {
            throw new TransactionException("ConcursoMateriaDAO.insert (" + sqle.getMessage() + ")");
        }
    }

    public static void update(ConcursoMateria concursoMateria, Connection conn) throws TransactionException {

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("UPDATE CONCURSO_MATERIA    ");
        sbSql.append("   SET ID_SITUACAO     = ? ");
        sbSql.append(" WHERE CD_CONCURSO     = ? ");
        sbSql.append("   AND CD_MATERIA      = ? ");
        try {
            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            int pos = 1;
            setInt(ps, pos++, concursoMateria.getSituacao().getCodigo());
            setInt(ps, pos++, concursoMateria.getConcurso().getCodigo());
            setInt(ps, pos++, concursoMateria.getMateria().getCodigo());
            ps.executeUpdate();
            close(ps);
        } catch (SQLException sqle) {
            throw new TransactionException("ConcursoMateriaDAO.update (" + sqle.getMessage() + ")");
        }
    }

    public static void delete(ConcursoMateria concursoMateria, Connection conn) throws TransactionException {

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("DELETE FROM CONCURSO_MATERIA  ");
        sbSql.append(" WHERE CD_CONCURSO = ? ");
        sbSql.append("   AND CD_MATERIA  = ? ");
        try {
            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            setInt(ps, 1, concursoMateria.getConcurso().getCodigo());
            setInt(ps, 2, concursoMateria.getMateria().getCodigo());
            ps.executeUpdate();
            close(ps);
        } catch (SQLException sqle) {
            throw new TransactionException("ConcursoMateriaDAO.delete (" + sqle.getMessage() + ")");
        }
    }

    public static void deleteByConcurso(Concurso concurso, Connection conn) throws TransactionException {

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("DELETE FROM CONCURSO_MATERIA  ");
        sbSql.append(" WHERE CD_CONCURSO = ? ");
        try {
            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            setInt(ps, 1, concurso.getCodigo());
            ps.executeUpdate();
            close(ps);
        } catch (SQLException sqle) {
            throw new TransactionException("ConcursoMateriaDAO.deleteByConcurso (" + sqle.getMessage() + ")");
        }
    }

    private static ConcursoMateria fromResultSet(ResultSet rs) throws SQLException {

        // CONCURSO MATERIA
        ConcursoMateria concursoMateria = new ConcursoMateria();
        // CONCURSO
        Concurso concurso = new Concurso();
        concurso.setCodigo(getInt(rs, "CD_CONCURSO"));
        concurso.getInstituicao().setCodigo(getInt(rs, "CD_INSTITUICAO"));
        concurso.getOrgao().setCodigo(getInt(rs, "CD_ORGAO"));
        concurso.setIdentificacao(getString(rs, "TX_INDENTIFICACAO"));
        concurso.setNivel(ConcursoNivel.getNivelById(getByte(rs, "ID_NIVEL")));
        concurso.setDataProva(getDate(rs, "DT_PROVA"));
        concurso.setSituacao(Situacao.getSituacaoById(getByte(rs, "ID_SITUACAO_CONCURSO")));
        concurso.setDataCadastro(getDateFromTimestamp(rs, "TS_CADASTRO_CONCURSO"));
        concursoMateria.setConcurso(concurso);
        // MATERIA
        Materia materia = new Materia();
        materia.setCodigo(getInt(rs, "CD_MATERIA"));
        materia.setNome(getString(rs, "TX_NOME"));
        materia.setSituacao(Situacao.getSituacaoById(getByte(rs, "ID_SITUACAO_MATERIA")));
        materia.setDataCadastro(getDateFromTimestamp(rs, "TS_CADASTRO_MATERIA"));
        concursoMateria.setMateria(materia);
        //
        concursoMateria.setSituacao(Situacao.getSituacaoById(getByte(rs, "ID_SITUACAO")));
        //
        return concursoMateria;
    }

}
